﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aulac.Rsc.Bss.Tags;
using Aulac.Rsc.Bss;
using Aulac.Global.Common;
using Aulac.Global.Xml;
using Aulac.Srv.Data.XmlQuery;
using System.Xml;
using Aulac.Srv.Db.Common;
using System.Data;

namespace Aulac.Srv.Db.Data.Ems.Items.RptLists
{
    partial class BuyCapAss
    {
        /// <summary>
        /// ExecQuery Chi tiet mot phieu mua hang.
        /// </summary>
        private XmlDataDocument ExecQueryGetDetailBuyCapAss(string pVchBuyCapAssPrkID)
        {
            try
            {
                SrvExecXmlQueryData query = new SrvExecXmlQueryData(DbDataProvider);

                query.SetVchHeaderElm(QueryCmdTypes.SelectData);
                string sqlQueryH = string.Format("SELECT {0}", StringHelper.ConvertArrayToString(", ", BuyCapAssInfo.mLstVchBuyHeader.ToArray()));
                sqlQueryH += string.Format(" FROM {0}", BuyCapAssInfo.mTbBuyHeader);
                sqlQueryH += string.Format(" WHERE {0} = @{0}", tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID);
                query.ObjQueryVchHeader.SqlQueryString = sqlQueryH;
                query.ObjQueryVchHeader.AddValueParamFilterItem(tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, pVchBuyCapAssPrkID);

                query.SetVchLinesElm(QueryCmdTypes.SelectData);
                //string sqlQueryL = string.Format("SELECT L.{0}, dbo.Dm_MdnItems.ItemGrpID", "*");
                //sqlQueryL += string.Format(" FROM {0} AS L ", BuyCapAssInfo.mTbBuyLine);
                //sqlQueryL += string.Format("LEFT OUTER JOIN dbo.Dm_MdnItems ON L.ItemPrkID = dbo.Dm_MdnItems.ItemPrkID");
                //sqlQueryL += string.Format(" WHERE (L.{0} = @{0})", tagVch.VchBuyLineCapAss.VchBuyCapAssPrkID);
                string sqlQueryL = string.Format("SELECT dbo.Dm_MdnItems.ItemName, dbo.Dm_MdnGrpItems.ItemGrpID, dbo.VchLineBuyItem.VchBuyCapAssPrkID, dbo.VchLineBuyItem.ItemLine, dbo.VchLineBuyItem.ItemPrkID, dbo.VchLineBuyItem.NoteLine, dbo.VchLineBuyItem.Unit, dbo.VchLineBuyItem.Quantity, dbo.VchLineBuyItem.UnitPrice, dbo.VchLineBuyItem.AmtLineInExc, dbo.VchLineBuyItem.VAT, dbo.VchLineBuyItem.AmtTaxLineInExc, dbo.VchLineBuyItem.Specification FROM dbo.Dm_MdnGrpItems LEFT OUTER JOIN dbo.Dm_MdnItems ON dbo.Dm_MdnGrpItems.ItemGrpID = dbo.Dm_MdnItems.ItemGrpID RIGHT OUTER JOIN dbo.VchLineBuyItem ON dbo.Dm_MdnItems.ItemPrkID = dbo.VchLineBuyItem.ItemPrkID WHERE (dbo.VchLineBuyItem.VchBuyCapAssPrkID = '{0}')", pVchBuyCapAssPrkID);
                query.ObjQueryVchLines.SqlQueryString = sqlQueryL;
                query.ObjQueryVchLines.AddValueParamFilterItem(tagVch.VchBuyLineItem.VchBuyCapAssPrkID, tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, pVchBuyCapAssPrkID);
            
                
                return query.ExecureQuery();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// ExecQuery Danh sach cac phieu mua hang.
        /// </summary>
        private XmlDataDocument ExecQueryGetListDetailBuyCapAss(string pVchDateFrom, string pVchDateTo)
        {
            string sqlQuery = string.Format("SELECT h.{0}", StringHelper.ConvertArrayToString(" ,h.", BuyCapAssInfo.mLstVchBuyHeader.ToArray()));
            sqlQuery += string.Format(" FROM {0} AS h ", BuyCapAssInfo.mTbBuyHeader);
            sqlQuery += string.Format(" WHERE (h.{0} BETWEEN ? AND ?)", tagVch.VchBuyHeaderItem.VchDate);
            try
            {
                DbDataReader = DbDataProvider.ExecuteReader(mDbCmd, sqlQuery, pVchDateFrom, pVchDateTo);
                XmlDataDocument xmlDoc = CreateXmlDocument("", "");
                XmlElement eVLines = XmlProcess.AddVchLinesElm(ref xmlDoc);
                while (DbDataReader.Read())
                {
                    XmlElement eLine = XmlProcess.AddVchLineItemElm(eVLines);
                    DbDataProvider.GetValuesToXml(DbDataReader, ref eLine, BuyCapAssInfo.mLstVchBuyHeader.ToArray());
                }
                DbDataReader.Close();
                return xmlDoc;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Add mot phieu mua hang.
        /// </summary>
        private XmlDataDocument ExecQueryAddVchHeaderBuyCapAss(XmlElement eTmpH)
        {
            try
            {

                //XmlElement eHeader = XmlProcess.GetVchHeaderElm(xmlData);
                //string sqlQueryH = SqlHelper.CreateSqlInsert(TableNames.VchHeaderBuyItem, sArrH);
                //DbDataProvider.AddParamToCmd(mDbCmd, eHeader, sArrH.ToArray());
                //DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQueryH);
                
                //// add Line?
                //XmlElement eLine = XmlProcess.GetVchLinesElm(xmlData);
                //string sqlQueryL = SqlHelper.CreateSqlInsert(TableNames.VchLineBuyItem, sArrL);
                //DbDataProvider.AddParamToCmd(mDbCmd, eLine, sArrL.ToArray());
                //DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQueryL);
                SrvExecXmlQueryData query = new SrvExecXmlQueryData(this.DbDataProvider);
                XmlElement eHeader = query.SetVchHeaderElm(QueryCmdTypes.AddDataTable);
                query.ObjQueryVchHeader.AddFieldPrk(tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, DbType.Decimal, KeyTypes.AutoNumber);
                query.ObjQueryVchHeader.TableName = BuyCapAssInfo.mTbBuyHeader;
                query.ImportData(query.ObjQueryVchHeader, ref eHeader, eTmpH);
                //query.ObjQueryVchHeader.AddValueParamFilterItem(tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, pVchBuyCapAssPrkID);
                return query.ExecureQuery();
            }
            catch (Exception Ex )
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Cap nhat mot Phieu mua hang.
        /// </summary>

        //private bool ExecQueryUpdateVchBuyCapAss(XmlElement eVch, ref string pVchBuyCapAssPrkID, ref string pVchTypeID)
        //{

        //    SrvExecXmlQueryData query = new SrvExecXmlQueryData(DbDataProvider);
        //    XmlElement eHeader = query.SetVchHeaderElm();
        //    query.ExecureQuery();

            
        //    XmlElement eHeaders = XmlProcess.GetVchHeaderElm(eVch);
        //    pVchBuyCapAssPrkID = XmlProcess.GetValueAtrb(eHeaders, tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID);
        //    string sVchDate = XmlProcess.GetValueAtrb(eHeaders, tagVch.VchBuyHeaderItem.VchDate);
        //    pVchTypeID = XmlProcess.GetValueAtrb(eHeaders, tagVch.VchBuyHeaderItem.VchTypeID);

        //    string[] arr = BuyCapAssInfo.mLstVchBuyHeader.GetRange(1, BuyCapAssInfo.mLstVchBuyHeader.Count - 1).ToArray();
        //    sqlQuery = SqlHelper.CreateSqlUpdate(TableNames.VchHeaderBuyCapAss, arr);
        //    sqlQuery += string.Format(" WHERE {0} = ?", tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID);
        //    DbDataProvider.AddParamToCmd(mDbCmd, eHeaders, arr);
        //    DbDataProvider.AddParamToCmd(mDbCmd, false, pVchBuyCapAssPrkID);

        //    return DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery) > 0;
        //}

        private XmlDataDocument ExecQueryUpdateVchHeaderBuyCapAss(string pVchBuyCapAssPrkID, XmlElement eTmpH)
        {
            try
            {
                SrvExecXmlQueryData query = new SrvExecXmlQueryData(this.DbDataProvider);
                XmlElement eHeader = query.SetVchHeaderElm(QueryCmdTypes.UpdateDataTable);
                query.ObjQueryVchHeader.AddFieldPrk(tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, DbType.Decimal, KeyTypes.AutoNumber);
                query.ObjQueryVchHeader.TableName = BuyCapAssInfo.mTbBuyHeader;
                query.ImportData(query.ObjQueryVchHeader, ref eHeader, eTmpH);
                query.ObjQueryVchHeader.AddValueParamFilterItem(tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, pVchBuyCapAssPrkID);
                return query.ExecureQuery();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        private void ExecQueryAddVchLineBuyCapAss(XmlNodeList pListLine, string pVchBuyCapAssPrkID)
        {
            try
            {
                SrvExecXmlQueryData query = new SrvExecXmlQueryData(this.DbDataProvider);                
                XmlElement eVLines = query.SetVchLinesElm(QueryCmdTypes.AddDataTable);
                query.ObjQueryVchLines.AddFieldPrk(tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, DbType.Decimal, KeyTypes.None, RefValueTypes.InnerLineElm);
                query.ObjQueryVchLines.TableName = BuyCapAssInfo.mTbBuyLine;

                foreach (XmlElement iL in pListLine)
                {
                    XmlElement eLine = XmlProcess.AddVchLineItemElm(eVLines);
                    query.ImportData(query.ObjQueryVchLines, ref eLine, iL);
                    XmlProcess.SetAtrb(ref eLine, tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, pVchBuyCapAssPrkID);
                }
                //for (int i = 0; i < pListLine.Count; i++)
                //{
                //    XmlElement eTmp = pListLine[i] as XmlElement;
                //    XmlElement eLine = XmlProcess.AddVchLineItemElm(eVLines);
                //    query.ImportData(query.ObjQueryVchLines, ref eLine, eTmp);
                //    XmlProcess.SetAtrb(ref eTmp, tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, pVchBuyCapAssPrkID);
                //}
                query.ExecureQuery();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }
        /// <summary>
        /// Xoa mot Phieu mua hang.
        /// </summary>
        private XmlDataDocument ExecQueryDelVchBuyCapAss(string sVchBuyCapAssPrkID)
        {
            try
            {
                string sqlQureyH = string.Format("DELETE FROM {0}",  BuyCapAssInfo.mTbBuyHeader);
                sqlQureyH += string.Format(" WHERE {0} = ?",tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID);
                string sqlQueryL = string.Format("DELETE FROM {0}", BuyCapAssInfo.mTbBuyLine);
                sqlQueryL += string.Format(" WHERE {0} = ?", tagVch.VchBuyLineItem.VchBuyCapAssPrkID);
                try
                {
                    DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQureyH, sVchBuyCapAssPrkID);
                    XmlDataDocument xmlDoc = CreateXmlDocument("", "");
                    XmlProcess.SetAtrbVchHeader(xmlDoc, tagVch.VchBuyHeaderItem.VchBuyCapAssPrkID, sVchBuyCapAssPrkID);
                    DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQueryL, sVchBuyCapAssPrkID);
                    return xmlDoc;
                }
                catch (Exception Ex)
                {
                    throw Ex;
                }
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }
        private bool DelVchBuyCapAssLine(string pVoucherID)
        {

            sqlQuery = SqlHelper.CreateSqlDelete(BuyCapAssInfo.mTbBuyLine, tagVch.VchBuyLineItem.VchBuyCapAssPrkID);
            return DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery, pVoucherID) >= 0;
        }

        //private bool AddVListVchLine(XmlNodeList pListLine, string pVchPrkID)
        //{
            
        //    try
        //    {
        //        sqlQuery = SqlHelper.CreateSqlInsert(TableNames.VchLineBuyCapAss,BuyCapAssInfo.mLstVchBuyLine.ToArray());

        //        for (int i = 0; i < pListLine.Count; i++)
        //        {
        //            XmlElement eLine = pListLine[i] as XmlElement;
        //            XmlProcess.SetAtrb(ref eLine, tagVch.VchBuyLineCapAss.VchBuyCapAssPrkID, pVchPrkID);
        //            XmlProcess.SetAtrb(ref eLine, tagVch.Inv.ItemLine, (i + 1));

        //            DbDataProvider.AddParamToCmd(mDbCmd, eLine, BuyCapAssInfo.mLstVchBuyLine.ToArray());
        //            DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery);
        //        }
        //        return true;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}
        
    }
}
